<!DOCTYPE html><html><head><title>MySQL学习笔记（Day003：升级/参数/连接/权限）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-1168" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-1169" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day003升级参数连接权限">MySQL学习笔记（Day003：升级/参数/连接/权限）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day003升级参数连接权限">MySQL学习笔记（Day003：升级/参数/连接/权限）</a><ul>
<li><a href="#一-数据库升级">一. 数据库升级</a><ul>
<li><a href="#1-环境说明">1. 环境说明：</a></li>
<li><a href="#2-环境举例">2. 环境举例：</a></li>
<li><a href="#3-版本升级">3. 版本升级</a></li>
<li><a href="#4关于降级问题的说明">4.关于降级问题的说明</a></li>
</ul>
</li>
<li><a href="#二-mysql的连接登录">二.  MySQL的连接登录</a><ul>
<li><a href="#1-几种登录方式">1. 几种登录方式</a></li>
<li><a href="#2-免密码登录">2. 免密码登录</a></li>
</ul>
</li>
<li><a href="#三-mysql-参数介绍和设置">三. MySQL 参数介绍和设置</a><ul>
<li><a href="#1-参数的分类">1. 参数的分类</a></li>
<li><a href="#2-查看参数">2. 查看参数</a></li>
<li><a href="#3-设置参数">3. 设置参数</a></li>
</ul>
</li>
<li><a href="#四-权限管理">四. 权限管理</a><ul>
<li><a href="#1-用户-ip的概念">1. “用户 + IP”的概念</a></li>
<li><a href="#2-用户权限管理">2. 用户权限管理</a></li>
<li><a href="#3-基本操作">3. 基本操作</a></li>
<li><a href="#4-撤销权限">4. 撤销权限</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
</div>

</div><div id="wmd-preview-section-1170" class="wmd-preview-section preview-content">

<h2 id="一-数据库升级">一. 数据库升级</h2>

</div><div id="wmd-preview-section-1171" class="wmd-preview-section preview-content">

<h3 id="1-环境说明">1. <strong>环境说明：</strong></h3>

<p>一般说来，MySQL数据库的二进制数据文件，也就是<code>my.cnf</code>中的配置项<code>datadir</code>所在的位置，和我们MySQL应用程序安装的位置，是分开的，仅仅通过配置项告诉MySQL，数据库的数据存在<code>datadir</code>这个目录下。当程序和数据分离以后，方便我们对数据库应用程序做版本的升级或者回退。</p>

</div><div id="wmd-preview-section-1172" class="wmd-preview-section preview-content">

<h3 id="2-环境举例">2. <strong>环境举例：</strong></h3>

<ul><li><p><em>MySQL安装目录：</em></p>

<ul>
<li><strong>MySQL 5.6.27:</strong> /usr/local/mysql-5.6.27-linux-glibc2.5-x86_64</li>
<li><strong>MySQL 5.7.9 :</strong> /usr/local/mysql-5.7.9-linux-glibc2.5-x86_64</li></ul></li>
<li><p><em>datadir目录：</em></p>

<ul>
<li>/data/mysq_data/</li></ul></li>
<li><p><em>初始环境：</em></p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">shell&gt; ll | grep mysql<br>lrwxrwxrwx   <span class="hljs-number">1</span> root root    <span class="hljs-number">34</span> Nov <span class="hljs-number">16</span> <span class="hljs-number">13</span>:<span class="hljs-number">40</span> mysql -&gt; mysql-<span class="hljs-number">5.6</span>.<span class="hljs-number">27</span>-linux-glibc2.<span class="hljs-number">5</span>-x86_64<br>drwxr-xr-x  <span class="hljs-number">13</span> root mysql  <span class="hljs-number">4096</span> Nov <span class="hljs-number">16</span> <span class="hljs-number">13</span>:<span class="hljs-number">37</span> mysql-<span class="hljs-number">5.6</span>.<span class="hljs-number">27</span>-linux-glibc2.<span class="hljs-number">5</span>-x86_64<br>drwxr-xr-x   <span class="hljs-number">9</span> <span class="hljs-number">7161</span> wheel <span class="hljs-number">4096</span> Oct <span class="hljs-number">12</span> <span class="hljs-number">00</span>:<span class="hljs-number">29</span> mysql-<span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-linux-glibc2.<span class="hljs-number">5</span>-x86_64<br><br>shell&gt; ll /data/mysql_data/<br>total <span class="hljs-number">13540</span><br>-rw-rw---- <span class="hljs-number">1</span> mysql mysql    <span class="hljs-number">65468</span> Nov <span class="hljs-number">16</span> <span class="hljs-number">13</span>:<span class="hljs-number">50</span> bin.<span class="hljs-number">000001</span><br>-rw-rw---- <span class="hljs-number">1</span> mysql mysql  <span class="hljs-number">1176237</span> Nov <span class="hljs-number">16</span> <span class="hljs-number">13</span>:<span class="hljs-number">50</span> bin.<span class="hljs-number">000002</span><br>-rw-rw---- <span class="hljs-number">1</span> mysql mysql       <span class="hljs-number">26</span> Nov <span class="hljs-number">16</span> <span class="hljs-number">13</span>:<span class="hljs-number">50</span> bin.index<br>-rw-rw---- <span class="hljs-number">1</span> mysql mysql     <span class="hljs-number">6882</span> Nov <span class="hljs-number">16</span> <span class="hljs-number">13</span>:<span class="hljs-number">50</span> error.log<br>-rw-rw---- <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">865</span> Nov <span class="hljs-number">16</span> <span class="hljs-number">13</span>:<span class="hljs-number">50</span> ib_buffer_pool<br>-rw-rw---- <span class="hljs-number">1</span> mysql mysql <span class="hljs-number">12582912</span> Nov <span class="hljs-number">16</span> <span class="hljs-number">13</span>:<span class="hljs-number">50</span> ibdata1<br>drwx------ <span class="hljs-number">2</span> mysql mysql     <span class="hljs-number">4096</span> Nov <span class="hljs-number">16</span> <span class="hljs-number">13</span>:<span class="hljs-number">50</span> mysql<br>drwx------ <span class="hljs-number">2</span> mysql mysql     <span class="hljs-number">4096</span> Nov <span class="hljs-number">16</span> <span class="hljs-number">13</span>:<span class="hljs-number">50</span> performance_schema<br>drwx------ <span class="hljs-number">2</span> mysql mysql     <span class="hljs-number">4096</span> Nov <span class="hljs-number">16</span> <span class="hljs-number">13</span>:<span class="hljs-number">49</span> <span class="hljs-built_in">test</span><br></code></pre></li>
</ul>

</div><div id="wmd-preview-section-1173" class="wmd-preview-section preview-content">

<h3 id="3-版本升级">3. 版本升级</h3>

</div><div id="wmd-preview-section-3041" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">shell&gt; /etc/init.d/mysqld stop  <span class="hljs-comment">#安全的停止数据库的运行</span><br>shell&gt; <span class="hljs-built_in">cd</span> /usr/<span class="hljs-built_in">local</span>/<br>shell&gt; unlink mysql<br>shell&gt; ln <span class="hljs-operator">-s</span> mysql-<span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-linux-glibc2.<span class="hljs-number">5</span>-x86_64 mysql <br>        <span class="hljs-comment">#此时，MySQL的应用程序版本已经升级完成</span><br>        <span class="hljs-comment">#/etc/init.d/mysqld</span><br>        <span class="hljs-comment">#/etc/profile中PATH增加的/usr/local/mysql/bin</span><br>        <span class="hljs-comment">#都不需要做任何的改变，即可将当前系统的mysql版本升级完成</span><br>        <span class="hljs-comment">#注意：此时只是应用程序升级完成，系统表仍然还是5.6的版本</span><br><br><br>shell&gt; <span class="hljs-built_in">cd</span> /usr/<span class="hljs-built_in">local</span>/mysql<br>shell&gt; chown root.mysql . -R<br><span class="hljs-comment">#5.7.x -&gt; 5.6.X 降级存在问题，这里暂且注释掉</span><br><span class="hljs-comment">#shell&gt; cp /data/mysql_data/mysql /你的备份路径/mysql_5_6_27.backup -r</span><br>       <span class="hljs-comment">#该步骤将mysql5.6.27版本的系统表进行了备份，以便将来可以回退</span><br><br>shell&gt; /etc/init.d/mysqld start <br><span class="hljs-comment">#此时 /etc/init.d/mysqld start  # 可以启动</span><br><span class="hljs-comment">#     且可以使用 mysql -u root -p （原密码） 进入数据库</span><br><span class="hljs-comment">#     show databases;存在test表，而没有sys表（数据的二进制文件兼容）</span><br><span class="hljs-comment">#     但是如果去看error.log会发现好多的WARNNING</span><br><span class="hljs-comment">#     所以，这个时候我们要去 upgrade 去升级</span><br><br><br>shell&gt; mysql_upgrade -p <span class="hljs-operator">-s</span>  <br>        <span class="hljs-comment">#参数 -s 一定要加,表示只更新系统表，-s: upgrade-system-tables</span><br>        <span class="hljs-comment">#如果不加-s,则会把所有库的表以5.7.9的方式重建，线上千万别这样操作</span><br>        <span class="hljs-comment">#因为数据库二进制文件是兼容的，无需升级</span><br>        <span class="hljs-comment">#什么时候不需要-s ? 当一些老的版本的存储格式需要新的特性，</span><br>        <span class="hljs-comment">#                 来提升性能时，不加-s</span><br>        <span class="hljs-comment">#即使通过slave进行升级，也推荐使用该方式升级，速度比较快</span><br><br>Enter password: <br>The --upgrade-system-tables option was used, databases wont be touched.<br>Checking <span class="hljs-keyword">if</span> update is needed.<br>Checking server version.<br>Running queries to upgrade MySQL server.<br>Upgrading the sys schema.<br>Upgrade process completed successfully.<br>Checking <span class="hljs-keyword">if</span> update is needed.<br><br>shell&gt; mysql -u root -p<br>Enter password: <br>Welcome to the MySQL monitor.  Commands end with ; or \g.<br>Your MySQL connection id is <span class="hljs-number">4</span><br>Server version: <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-log MySQL Community Server (GPL)<br><br>Copyright (c) <span class="hljs-number">2000</span>, <span class="hljs-number">2015</span>, Oracle and/or its affiliates. All rights reserved.<br><br>Oracle is a registered trademark of Oracle Corporation and/or its<br>affiliates. Other names may be trademarks of their respective<br>owners.<br><br>Type <span class="hljs-string">'help;'</span> or <span class="hljs-string">'\h'</span> <span class="hljs-keyword">for</span> help. Type <span class="hljs-string">'\c'</span> to clear the current input statement.<br><br>mysql&gt; show databases;<br>+--------------------+<br>| Database           |<br>+--------------------+<br>| information_schema |<br>| mysql              |  <span class="hljs-comment"># 这个就是升级后的系统库，如果回退，将备份的拷贝回来覆盖即可</span><br>| performance_schema |<br>| sys                |  <span class="hljs-comment"># 5.7 新的sys库</span><br>| <span class="hljs-built_in">test</span>               |  <span class="hljs-comment"># 5.6 中的test库</span><br>+--------------------+<br><span class="hljs-number">5</span> rows <span class="hljs-keyword">in</span> <span class="hljs-built_in">set</span> (<span class="hljs-number">0.00</span> sec)<br></code></pre>

<blockquote>
  <p><code>5.1.X</code>、<code>5.5.X</code> 、<code>5.6.X</code> 是可以直接通过该方式升级到<code>5.7.X</code>。<code>5.0.X</code>未知，需要测试</p>
</blockquote>

<p><strong>注意：</strong> <br>
如果原来数据二进制文件保存在<strong>/usr/local/<code>mysql-5.6.27</code>-linux-glibc2.5-x86_64/data</strong>目录下,在升级之前，要么将该目录的数据拷贝到新的你指定的data目录（比如<strong>/usr/local/<code>mysql-5.7.9</code>-linux-glibc2.5-x86_64/data</strong> ），要么修改<code>my.cnf</code>，将<code>datadir</code>指向<code>/usr/local/mysql-5.6.27-linux-glibc2.5-x86_64/data</code>，总之一定要确保<code>my.cnf</code>中的数据位置和你实际的数据位置是一致的，不管是默认的也好，还是你<code>datadir</code>指定的也好</p>

</div><div id="wmd-preview-section-6354" class="wmd-preview-section preview-content">

<h3 id="4关于降级问题的说明">4.关于降级问题的说明</h3>

<p>通过覆盖<code>mysql</code>系统表的方式存在问题，会导致启动不起来。官方建议如下：</p>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/downgrading.html#downgrade-procedure-inplace" target="_blank">官方MySQL5.7降级建议</a> <br>
  上述建议中使用的SQL语句可在<code>mysql5.7</code>的源码的<code>srcipts/mysql_system_tables_fix_for_downgrade.sql</code>中找到，或者直接运行这个sql脚本。</p>
</blockquote>

<p>姜老师测试后发现，有bug; 可以启动，但是原来的用户表，无法访问。 <br>
当前记录时间是2015-11-17，等待下一步解决方案。</p>

<hr></div><div id="wmd-preview-section-4400" class="wmd-preview-section preview-content">

<h2 id="二-mysql的连接登录">二.  MySQL的连接登录</h2>

</div><div id="wmd-preview-section-4401" class="wmd-preview-section preview-content">

<h3 id="1-几种登录方式">1. <strong>几种登录方式</strong></h3>

<ul><li><p>方式一 <code>mysql -p</code></p>

<ul>
<li>该方法默认使用root用户, 可使用<code>select user();</code>查看当前用户</li></ul></li>
<li><p>方式二 <code>mysql -S /tmp/mysql.sock -u root -p</code>  <em>密码A</em></p>

<ul>
<li>该方法适用于在安装MySQL主机上进行本地登录</li></ul></li>
<li><p>方式三 <code>mysql -h 127.0.0.1 -u root -p</code> <em>密码B</em></p>

<ul>
<li>使用<code>'root'@'127.0.0.1'</code>这个用户登录</li></ul></li>
<li><p>方式四 <code>mysql -h localhost -u root -p</code> <em>密码A</em></p>

<ul>
<li>该方式等价与【方式二】，且和【方式三】属于两个不同的“用户”</li></ul></li>
</ul>

</div><div id="wmd-preview-section-4402" class="wmd-preview-section preview-content">

<h3 id="2-免密码登录">2. 免密码登录</h3>

<ul><li><p>方式一 <code>my.cnf</code>增加<code>[client]</code>标签   </p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">[client]   <br>user=<span class="hljs-string">"root"</span>  <br>password=<span class="hljs-string">"你的密码"</span>  <br></code></pre>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><br><span class="hljs-comment">#单对定义不同的客户端</span><br><br>[mysql] <span class="hljs-comment"># 这个是给/usr/loca/mysql/bin/mysql 使用的</span><br>user=root<br>password=<span class="hljs-string">"你的密码"</span><br><br>[mysqladmin] <span class="hljs-comment"># 这个是给/usr/local/mysql/bin/mysqladmin使用的</span><br>user=root<br>password=<span class="hljs-string">"你的密码"</span><br></code></pre>

<p><strong>每个不同的客户端需要定义不同的标签，使用<code>[client]</code>可以统一</strong></p></li>
<li><p>方式二  <code>login-path</code></p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">shell&gt; mysql_config_editor <span class="hljs-built_in">set</span> -G vm1 -S /tmp/mysql.sock -u root -p<br>Enter password [输入root的密码]<br><br>shell&gt; mysql_config_editor <span class="hljs-built_in">print</span> --all<br>[vm1]<br>user=root<br>password=*****<br>socket=/tmp/mysql.sock<br><br><br><span class="hljs-comment">#login</span><br><br>shell&gt; mysql --login-path=vm1 <span class="hljs-comment"># 这样登录就不需要密码，且文件二进制存储 ,位置是 ~/.mylogin.cnf</span><br></code></pre>

<p><strong>该方式相对安全。如果server被黑了，该二进制文件还是会被破解</strong></p></li>
<li><p>方式三 <code>~/.my.cnf</code>, 自己当前家目录</p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><br><span class="hljs-comment">#Filename: ~/.my.cnf</span><br><br>[client]<br>user=<span class="hljs-string">"root"</span><br>password=<span class="hljs-string">"你的密码"</span><br></code></pre></li>
</ul>

<hr>

</div><div id="wmd-preview-section-4403" class="wmd-preview-section preview-content">

<h2 id="三-mysql-参数介绍和设置">三. MySQL 参数介绍和设置</h2>

</div><div id="wmd-preview-section-4404" class="wmd-preview-section preview-content">

<h3 id="1-参数的分类">1. 参数的分类</h3>

<ul><li>全局参数：GLOBAL <br>
<ul>
<li>可修改参数</li>
<li>不可修改参数</li></ul></li>
<li>会话参数：SESSION <br>
<ul>
<li>可修改参数</li>
<li>不可修改参数</li></ul></li>
</ul>

<blockquote>
  <p>1: 用户可在线修改<code>非只读参数</code>，<code>只读参数</code>只能预先在配置文件中进行设置，通过重启数据库实例,方可生效。  </p>
  
  <p>2: 所有的在线修改过的参数(GLOBAL/SESSION)，在重启后，都会丢失，不会写如<code>my.cnf</code>，无法将修改进行持久化</p>
  
  <p>3: 有些参数，即存在于<code>GLOBAL</code>又存在于<code>SESSION</code>, 比如<code>autocommit</code> (PS：MySQL默认是提交的)</p>
</blockquote>

</div><div id="wmd-preview-section-4405" class="wmd-preview-section preview-content">

<h3 id="2-查看参数">2. 查看参数</h3>

</div><div id="wmd-preview-section-4406" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">mysql&gt; show variables; <span class="hljs-comment"># 显示当前mysql的所有参数，且无隐藏参数</span><br>mysql&gt; show variables like <span class="hljs-string">"max_%"</span>; <span class="hljs-comment">#查以max_开头的变量</span><br></code></pre>

</div><div id="wmd-preview-section-4407" class="wmd-preview-section preview-content">

<h3 id="3-设置参数">3. 设置参数</h3>

<ul><li><p>设置全局(GLOBAL)参数</p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">mysql&gt; <span class="hljs-built_in">set</span> global slow_query_<span class="hljs-built_in">log</span> = off; <span class="hljs-comment">#不加global，会提示错误</span><br>                                        <span class="hljs-comment">#slow_query_log是全局参数</span><br><br>mysql&gt; <span class="hljs-built_in">set</span> slow_query_<span class="hljs-built_in">log</span> = off;  <span class="hljs-comment"># 下面就报错了，默认是会话参数</span><br>ERROR <span class="hljs-number">1229</span> (HY000): Variable <span class="hljs-string">'slow_query_log'</span> is a GLOBAL variable and should be <span class="hljs-built_in">set</span> with SET GLOBAL<br></code></pre></li>
<li><p>设置会话(SESSION)参数</p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">mysql&gt; <span class="hljs-built_in">set</span> autocommit = <span class="hljs-number">0</span>;  <span class="hljs-comment"># 当前会话生效</span><br><br><span class="hljs-comment"># 或者</span><br><br>mysql&gt; <span class="hljs-built_in">set</span> session autocommit = <span class="hljs-number">0</span>;  <span class="hljs-comment"># 当前会话生效</span><br></code></pre>

<p><code>autocommit</code>同样在<code>GLOBAL</code>中, 也有同样的参数</p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">mysql&gt; <span class="hljs-built_in">set</span> global autocommit = <span class="hljs-number">1</span>; <span class="hljs-comment">#当前实例，全局生效</span><br></code></pre>

<p><strong>注意：如果这个时候/etc/init.d/mysqld restart, 则全局的autocommit的值会变成默认值，或者依赖于my.cnf的设置值。</strong></p>

<p>执行的效果如下：</p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">mysql&gt; show variables like <span class="hljs-string">"slow%"</span>; <span class="hljs-comment"># 原值为ON</span><br>+---------------------+----------+<br>| Variable_name       | Value    |<br>+---------------------+----------+<br>| slow_launch_time    | <span class="hljs-number">2</span>        |<br>| slow_query_<span class="hljs-built_in">log</span>      | OFF      |<br>| slow_query_<span class="hljs-built_in">log</span>_file | slow.log |<br>+---------------------+----------+<br><span class="hljs-number">3</span> rows <span class="hljs-keyword">in</span> <span class="hljs-built_in">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; select @@session.autocommit; <span class="hljs-comment"># 等价于 slect @@autocomit;</span><br>+----------------------+<br>| @@session.autocommit |<br>+----------------------+<br>|                    <span class="hljs-number">0</span> |<br>+----------------------+<br><span class="hljs-number">1</span> row <span class="hljs-keyword">in</span> <span class="hljs-built_in">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; select @@global.autocommit;       <br>+---------------------+<br>| @@global.autocommit |<br>+---------------------+<br>|                   <span class="hljs-number">1</span> |<br>+---------------------+<br><span class="hljs-number">1</span> row <span class="hljs-keyword">in</span> <span class="hljs-built_in">set</span> (<span class="hljs-number">0.00</span> sec)<br></code></pre></li>
</ul>

</div><div id="wmd-preview-section-4408" class="wmd-preview-section preview-content">

<hr>

</div><div id="wmd-preview-section-4409" class="wmd-preview-section preview-content">

<h2 id="四-权限管理">四. 权限管理</h2>

</div><div id="wmd-preview-section-4410" class="wmd-preview-section preview-content">

<h3 id="1-用户-ip的概念">1. “用户 + IP”的概念</h3>

<p>MySQL中同一个用户名，比如Bob,能否登录，以及用什么密码登录，可以访问什么库等等，都需要加上IP，才可以表示一个完整的用户标识</p>

<blockquote>
  <p><code>bob@127.0.0.1</code> 和 <code>bob@loalhost</code> 以及 <code>bob@192.168.1.100</code> 这三个其实是<code>不同</code>的 <strong>用户标识</strong> </p>
</blockquote>

</div><div id="wmd-preview-section-4411" class="wmd-preview-section preview-content">

<h3 id="2-用户权限管理">2. 用户权限管理</h3>

<ul><li><p><strong>系统表权限信息:</strong></p>

<ul>
<li><strong>a) 用户名和IP是否允许</strong></li>
<li><strong>b) 查看mysql.user表</strong>  <code>// 查看全局所有库的权限</code></li>
<li><strong>c) 查看mysql.db表</strong>  <code>// 查看指定库的权限</code></li>
<li><strong>d) 查看mysql.table_priv表</strong> <code>// 查看指定表的权限</code></li>
<li><strong>e) 查看mysql.column_priv表</strong> <code>// 查看指定列的权限</code></li></ul>

<p><strong><em>tips</em></strong>: mysql&gt; desc [tablename]; 可以查看表的结构信息；</p></li>
<li><p><strong>常用权限：</strong></p>

<ul>
<li>SQL语句：SELECT、INSERT、UPDATE、DELETE、INDEX</li>
<li>存储过程：CREATE ROUTINE、ALTER ROUTINE、EXECUTE、TRIGGER</li>
<li>管理权限：SUPER、RELOAD、SHOW DATABASE、SHUTDOWN、</li></ul>

<p><a href="https://dev.mysql.com/doc/refman/5.x/en/privileges-provided.html" target="_blank">所有权限猛戳这里</a></p></li>
<li><p><strong>可选资源:</strong></p>

<ul>
<li>MAX_QUERIES_PER_HOUR <em>count</em></li>
<li>MAX_UPDATES_PER_HOUR <em>count</em></li>
<li>MAX_CONNECTIONS_PER_HOUR <em>count</em></li>
<li>MAX_USER_CONNECTIONS <em>count</em></li></ul>

<p><strong><em>tips:</em></strong>只能精确到小时，对于部分场景不适用，可以考虑中间件方式</p></li>
<li><p><strong>显示当前用户的权限</strong></p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><br><span class="hljs-comment">#这三个是同一个意思</span><br><br>mysql&gt; show grants;<br>mysql&gt; show grants <span class="hljs-keyword">for</span> current_user;<br>mysql&gt; show grants <span class="hljs-keyword">for</span> current_user();<br></code></pre></li>
</ul>

</div><div id="wmd-preview-section-4412" class="wmd-preview-section preview-content">

<h3 id="3-基本操作">3. 基本操作</h3>

</div><div id="wmd-preview-section-4413" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">mysql&gt; create user <span class="hljs-string">'bob'</span>@<span class="hljs-string">'127.0.0.1'</span> identified by <span class="hljs-string">'123'</span>; <br>       <span class="hljs-comment">#创建一个认证用户为'bob'@'127.0.0.1',密码是123</span><br>mysql&gt; grant all on NWDB.* to <span class="hljs-string">'bob'</span>@<span class="hljs-string">'127.0.0.1'</span>;<br>       <span class="hljs-comment">#授予他NWDB库下面所有表的所有访问权限; *.*表示所有库的所有表</span><br><br>mysql&gt; grant all on NWDB.* to <span class="hljs-string">'alice'</span>@<span class="hljs-string">'127.0.0.1'</span> identified by <span class="hljs-string">'123'</span>;<br>       <span class="hljs-comment">#这个grant语句会搜索用户，如果用户不存在，则自动创建用户，</span><br>       <span class="hljs-comment">#如果不带identified by, 则该用户名密码为空</span><br><br>mysql&gt; grant all on *.* to <span class="hljs-string">'tom'</span>@<span class="hljs-string">'192.168.10.%'</span> identified by <span class="hljs-string">'123'</span> with grant option;<br>       <span class="hljs-comment">#表示这个用户'tom'@'127.0.0.1'可以访问所有库的所有表，</span><br>       <span class="hljs-comment">#同时，他还可以给其他用户授予权限(with grant option)，</span><br>       <span class="hljs-comment">#注意如果，*.*改成了某一个指定的非USER库，</span><br>       <span class="hljs-comment">#则tom没法去新建其他用户了，因为User库没有权限了</span><br>       <span class="hljs-comment">#192.168.10.% 表示属于192.168.10.0/24网段的用户可以访问</span><br></code></pre>

</div><div id="wmd-preview-section-4414" class="wmd-preview-section preview-content">

<h3 id="4-撤销权限">4. 撤销权限</h3>

<ul><li><code>revoke</code> 关键字，该关键字只删除用户权限，不删除用户</li>
<li><code>revoke</code> 语法同<code>grant</code>一致, 从<code>grant ... to</code> 变为<code>revoke ... from</code></li>
</ul></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>